package com.mconnect.msurvey.controller;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import com.mconnect.msurvey.model.ResponderRecommended;
import com.mconnect.msurvey.util.HibernateUtil;

public class RecommendedManager extends HibernateUtil{
	
	@SuppressWarnings("unchecked")
	public List<ResponderRecommended> getRecommendedListAfterId(int id_responder){
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		session.getTransaction().begin();
        List<ResponderRecommended> responderList = new ArrayList<ResponderRecommended>();
        responderList = session.createQuery("select DISTINCT s.recommendedList from Responder s LEFT JOIN s.recommendedList where s.id_responder=" + id_responder).list();
        session.getTransaction().commit();
       
		return responderList;
        
	}
	
	@SuppressWarnings("unchecked")
	public List<ResponderRecommended> getRecommendListById_plan(int id_plan, int pageNumber, String firstname, String lastname, String mail, String phone, String status, String type){
		
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		session.getTransaction().begin();
		String region_Sql =" ", statusSql = " ", firstnameSql = " ", lastnameSql = " ", phoneSql =" ", mailSql = " ";
		int pageSize = 15;
		if("HR".equals(type)){
			region_Sql = " and region='HR' ";
		}else if("responder".equals(type)){
			region_Sql = " and region != 'HR' ";
		}
		if("TAK".equals(status)){
			statusSql = " and r.recConfirm='TAK' ";
		}else if("NIE".equals(status)){
			statusSql = " and r.recConfirm ='NIE' ";
		}
		if(!firstname.isEmpty()){
			firstnameSql = " and r.firstName like('%" + firstname +"%') ";
		}if(!lastname.isEmpty()){
			lastnameSql = " and r.lastName like('%" + lastname +"%') ";
		}if(!mail.isEmpty()){
			mailSql = " and r.mail like('%" + mail + "%') ";
		}if(!phone.isEmpty()){
			phoneSql = " and r.phone like('%" + phone + "%') ";
		}
		List<ResponderRecommended> responderList = new ArrayList<ResponderRecommended>();
		List<Integer> id_respondersDB = (List<Integer>) session.createSQLQuery("select DISTINCT survey.id_responder from survey left join responder on survey.id_responder = responder.id_responder where id_plan=" + id_plan + region_Sql  ).list(); 
		if(!id_respondersDB.isEmpty()){
			Query q = session.createQuery("select DISTINCT r from Responder s JOIN s.recommendedList r where s.id_responder in(" + getId_responderSQL(id_respondersDB) + ")" + statusSql + firstnameSql + lastnameSql + mailSql + phoneSql);
			q.setFirstResult(pageSize * (pageNumber - 1));
		    q.setMaxResults(pageSize);
		    responderList = q.list();
		}
	    
	    session.getTransaction().commit();
	    return responderList;
	}
	
	private String getId_responderSQL(List<Integer> id_respondersDB){
		int allResult = id_respondersDB.size();
		String id_responderSQL = "";
		for(int i=0; i<allResult; i++){
			id_responderSQL += + id_respondersDB.get(i) + ", ";
		}
		
		id_responderSQL = id_responderSQL.substring(0, id_responderSQL.length()-2);
		return id_responderSQL;
	}
	
//	public static void main(String[] args) {
//		RecommendedManager rm = new RecommendedManager();
//		List<ResponderRecommended> rmL = rm.getRecommendListById_plan(1, 1, "", "", "", "78", "all", "all");
//		for (ResponderRecommended responderRecommended : rmL) {
//			System.out.println(responderRecommended);
//		}
//	}
	
}
